package com.cms.biz.queryAnalyse.query;

import java.sql.ResultSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.builder.meta.bizmodel.SYS_MDL_CTN;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;

/**
 * <p>Title: 生产申请产品统计查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPSCSQCPFormQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;
	
	protected     String                   msTableName = "";
	
	protected     String                  msTableName1 = "";
	
	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		EFRowSet     queryRowset = EFRowSet.getInstance();
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
			
			// 获取查询数据
			queryDataset = queryDataObject(stmt, PO);
			queryRowset.setExtProperty(EMPQueryParamEnum.PAGEQUERY, PO.getValue(EMPQueryParamEnum.PAGEQUERY, null));
			queryRowset.setDataSet(EMPQueryParamEnum.QUERYRESULT, queryDataset);
			// 封装返回数据
		   	RO = new JResponseObject(queryRowset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 组织数据的过程
	 * @throws Exception
	 * @return TreeTableDataManager
	 */
	public EFDataSet queryDataObject(JStatement stmt, JParamObject PO) throws Exception {
		EFDataSet     queryDataset = null;
		EFDataSet       newDataset = EFDataSet.getInstance();
		EMPPageQuery     pageQuery = (EMPPageQuery) PO.getValue(EMPQueryParamEnum.PAGEQUERY, "");

		queryDataset = getFormDataSet(stmt, PO, SYS_MDL_CTN._BIZ_CTN_TYPE_JBDS_);

		if(pageQuery == null) return queryDataset;
		
		pageQuery.setTotalCount(queryDataset.getRowCount());
		newDataset = EMPLimitConvertUtil.getDataSetLimit(queryDataset, pageQuery.getPageNum(), pageQuery.getNumPerPage());
		return newDataset;
	}
	
	public EFDataSet getFormDataSet(JStatement stmt, JParamObject PO, String BIZ_CTN_TYPE) {
		ResultSet          rs = null;
		String         strSql = " select ";
		String         F_FLAG = PO.GetValueByParamName("F_FLAG", "");
		String         F_CPBH = PO.GetValueByParamName("F_CPBH", "");
		String         F_XMBH = PO.GetValueByParamName("F_XMBH", "");
		String         F_QSRQ = PO.GetValueByParamName("F_QSRQ", "");
		String         F_JZRQ = PO.GetValueByParamName("F_JZRQ", "");
		EFDataSet dictDataSet = EFDataSet.getInstance("SCSQMX");

		try {
			
			//F_FLAG为XMMX，则查询项目产品明细
			if(F_FLAG.equals("XMMX")) {
				//单据状态为1，并且走完流程的单据才能统计查询产品数量
				strSql = " select SCSQXX.F_XMBH, HTXMZD.F_XMMC, SCSQMX.F_CPBH, HTCPZD.F_CPMC, sum(F_SCSL) as F_SCSL"
					   + " from SCSQMX, SCSQXX, HTXMZD, HTCPZD "
					   + " where SCSQMX.F_CPBH like '" + F_CPBH + "%' and HTXMZD.F_XMBH = SCSQXX.F_XMBH and "
					   + " HTCPZD.F_CPBH = SCSQMX.F_CPBH and SCSQXX.F_GUID = SCSQMX.F_GUID and SCSQXX.F_DJZT = '1'"
					   + " and SCSQXX.F_ZDRQ >= '" + F_QSRQ + "' and SCSQXX.F_ZDRQ <= '" + F_JZRQ + "'"
					   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCSQ_FLOW'"
					   + " and SCSQMX.F_KJQJ = FLOW_TASK_END_LIST.BIZ_DATE and SCSQMX.F_DJBH = FLOW_TASK_END_LIST.BIZ_DJBH and "
					   + " SCSQMX.F_GUID = FLOW_TASK_END_LIST.BIZ_GUID)"
					   + " group by SCSQXX.F_XMBH, SCSQMX.F_CPBH"
					   + " order by SCSQXX.F_XMBH, SCSQMX.F_CPBH";
			}
			
			else if(F_FLAG.equals("DJMX")) {
				//单据状态为1，并且走完流程的单据才能统计查询产品数量
				strSql = " select SCSQXX.F_KJQJ, SCSQXX.F_DJBH, SCSQXX.F_GUID, SCSQXX.F_XMBH, HTXMZD.F_XMMC, SCSQMX.F_CPBH, HTCPZD.F_CPMC, sum(F_SCSL) as F_SCSL"
					   + " from SCSQMX, SCSQXX, HTXMZD, HTCPZD "
					   + " where SCSQMX.F_CPBH like '" + F_CPBH + "%' and HTXMZD.F_XMBH like '" + F_XMBH + "%' and HTXMZD.F_XMBH = SCSQXX.F_XMBH and "
					   + " HTCPZD.F_CPBH = SCSQMX.F_CPBH and SCSQXX.F_GUID = SCSQMX.F_GUID and SCSQXX.F_DJZT = '1'"
					   + " and SCSQXX.F_ZDRQ >= '" + F_QSRQ + "' and SCSQXX.F_ZDRQ <= '" + F_JZRQ + "'"
					   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCSQ_FLOW'"
					   + " and SCSQMX.F_KJQJ = FLOW_TASK_END_LIST.BIZ_DATE and SCSQMX.F_DJBH = FLOW_TASK_END_LIST.BIZ_DJBH and "
					   + " SCSQMX.F_GUID = FLOW_TASK_END_LIST.BIZ_GUID)"
					   + " group by SCSQXX.F_KJQJ, SCSQXX.F_DJBH, SCSQXX.F_GUID, SCSQXX.F_XMBH, SCSQMX.F_CPBH"
					   + " order by SCSQXX.F_KJQJ, SCSQXX.F_DJBH, SCSQXX.F_GUID, SCSQXX.F_XMBH, SCSQMX.F_CPBH";
			}
			
			else {
				//单据状态为1，并且走完流程的单据才能统计查询产品数量
				strSql = " select SCSQMX.F_CPBH, HTCPZD.F_CPMC, sum(F_SCSL) as F_SCSL from SCSQMX "
					   + " LEFT JOIN HTCPZD ON HTCPZD.F_CPBH = SCSQMX.F_CPBH " 
					   + " where SCSQMX.F_CPBH like '" + F_CPBH + "%'"
					   + " and exists(select 1 from SCSQXX where SCSQXX.F_GUID = SCSQMX.F_GUID and SCSQXX.F_DJZT = '1'"
					   + " and SCSQXX.F_ZDRQ >= '" + F_QSRQ + "' and SCSQXX.F_ZDRQ <= '" + F_JZRQ + "')"
					   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'SCSQ_FLOW'"
					   + " and SCSQMX.F_KJQJ = FLOW_TASK_END_LIST.BIZ_DATE and SCSQMX.F_DJBH = FLOW_TASK_END_LIST.BIZ_DJBH and "
					   + " SCSQMX.F_GUID = FLOW_TASK_END_LIST.BIZ_GUID)"
					   + " group by SCSQMX.F_CPBH"
					   + " order by SCSQMX.F_CPBH";
			}
			
			rs = stmt.executeQuery(strSql);
			dictDataSet = DataSetUtils.resultSet2DataSet(rs, dictDataSet);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, null, null);
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dictDataSet;
	}
}
